package soccerscope.util.analyze;

import java.util.ArrayList;
import java.sql.Connection;
import soccerscope.db.DatabaseConfig;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import soccerscope.model.PlayMode;
import java.sql.ResultSet;

/**
 * <p>
 * Title:
 * </p>
 * 
 * <p>
 * Description:
 * </p>
 * 
 * <p>
 * Copyright: Copyright (c) 2006
 * </p>
 * 
 * <p>
 * Company:
 * </p>
 * 
 * @author Linh Phong Le
 * @version 1.0
 */
public class DBAnalyzer {

  public ArrayList<Object[]> analyzerList = new ArrayList<Object[]>();
  // Zugang zu Datenbank-Server
  public static Connection   connection   = null;
  public String              sqlHost;
  public String              sqlDB;
  public String              sqlUser;
  public String              sqlPassword;

  public DBAnalyzer() {

    DatabaseConfig dbConfig = DatabaseConfig.getInstance();
    dbConfig.load();
    this.sqlHost = dbConfig.getSqlHost();
    this.sqlDB = dbConfig.getSqlDB();
    this.sqlUser = dbConfig.getSqlUser();
    this.sqlPassword = dbConfig.getSqlPassword();

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      DBAnalyzer.connection = DriverManager.getConnection(
          "jdbc:mysql://" + this.sqlHost + "/" + this.sqlDB, this.sqlUser,
          this.sqlPassword);
      System.out.println("[RobocupDatabase] Database connected");
    }
    catch (ClassNotFoundException ex) {
      ex.printStackTrace();
    }
    catch (IllegalAccessException ex) {
      ex.printStackTrace();
    }
    catch (InstantiationException ex) {
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  private String[] analyze(String name,
      int gameID,
      int playModeLeft,
      int playModeRight,
      boolean revertSort) {

    /*
     * String query = "SELECT client_scene.game_id, client_scene.play_mode,
     * client_scene.time, client_scene.id " + "FROM client_scene WHERE " +
     * "(client_scene.game_id = ?) AND (client_scene.play_mode = ?) GROUP BY
     * time";
     */
    String query = "SELECT client_scene.game_id, client_scene.play_mode, client_scene.time, client_scene.id, client_scene.ball_pos_y, client_scene.ball_pos_x " + "FROM client_scene WHERE " + "(client_scene.game_id = ?) AND (client_scene.play_mode = ?) GROUP BY ball_pos_x, ball_pos_y";

    if (revertSort) {
      query += " ORDER BY client_scene.time DESC";
    }
    int leftCount = 0, rightCount = 0;
    String leftTime = "", rightTime = "";

    try {
      PreparedStatement stm = DBAnalyzer.connection.prepareStatement(query);
      stm.setInt(1, gameID);
      stm.setInt(2, playModeLeft);

      ResultSet rs = stm.executeQuery();
      while (rs.next()) {
        leftCount++;
        if (leftTime.compareTo("") != 0) {
          leftTime = leftTime + ", ";
        }
        leftTime += rs.getInt("time");
      }
      if (leftTime.compareTo("") != 0) {
        leftTime = "(" + leftTime + ")";
      }

      rs.close();

      stm.setInt(2, playModeRight);

      rs = stm.executeQuery();
      while (rs.next()) {
        rightCount++;
        if (rightTime.compareTo("") != 0) {
          rightTime = rightTime + ", ";
        }
        rightTime += rs.getInt("time");
      }
      if (rightTime.compareTo("") != 0) {
        rightTime = "(" + rightTime + ")";
      }

      rs.close();

      stm.close();
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }

    // return leftCount + " " + leftTime + " : " + rightCount + " " + rightTime;
    String[] result = { name, leftCount + " " + leftTime, rightCount + " " + rightTime };
    return result;
  }

  public String[] goalAnalyze(int gameID) {

    return this.analyze("Goal", gameID, PlayMode.PM_AfterGoal_Left,
        PlayMode.PM_AfterGoal_Right, false);
  }

  public String[] goalGickAnalyze(int gameID) {

    return this.analyze("Goal Kick", gameID, PlayMode.PM_GoalKick_Left,
        PlayMode.PM_GoalKick_Right, false);
  }

  public String[] offSideAnalyze(int gameID) {

    return this.analyze("Off Side", gameID, PlayMode.PM_OffSide_Left,
        PlayMode.PM_OffSide_Right, false);
  }

  public String[] connerKickAnalyze(int gameID) {

    return this.analyze("Conner Kick", gameID, PlayMode.PM_CornerKick_Left,
        PlayMode.PM_CornerKick_Right, false);
  }

  public String[] backPassAnalyze(int gameID) {

    return this.analyze("Back Pass", gameID, PlayMode.PM_Back_Pass_Left,
        PlayMode.PM_Back_Pass_Right, false);
  }

  public String[] freeKickAnalyze(int gameID) {

    return this.analyze("Free Kick", gameID, PlayMode.PM_FreeKick_Left,
        PlayMode.PM_FreeKick_Right, false);
  }

  public String[] freeKickFaultAnalyze(int gameID) {

    return this.analyze("Free Kick Fault", gameID,
        PlayMode.PM_Free_Kick_Fault_Left, PlayMode.PM_Free_Kick_Fault_Right,
        false);
  }

  public String[] kickInAnalyze(int gameID) {

    return this.analyze("Kick In", gameID, PlayMode.PM_KickIn_Left,
        PlayMode.PM_KickIn_Right, true);
  }

  public String[] ballOutAnalyze(int gameID) {

    return this.analyze("Ball Out", gameID, PlayMode.PM_Foul_BallOut_Left,
        PlayMode.PM_Foul_BallOut_Right, false);
  }

  public Object[][] analyze(int gameID) {

    if (DBAnalyzer.connection == null) {
      return null;
    }

    Object[][] result = { this.goalAnalyze(gameID), this
        .goalGickAnalyze(gameID), this.offSideAnalyze(gameID), this
        .backPassAnalyze(gameID), this.connerKickAnalyze(gameID), this
        .freeKickAnalyze(gameID), this.freeKickFaultAnalyze(gameID), this
        .kickInAnalyze(gameID), this.ballOutAnalyze(gameID) };
    return result;
  }

  public static void main(String[] args) {

    DBAnalyzer dbanalyzer = new DBAnalyzer();
    System.out.println(dbanalyzer.goalAnalyze(17)[0]);
    /*
     * System.out.println("goalAnalyze " + dbanalyzer.goalAnalyze(17));
     * System.out.println("goalGickAnalyze " + dbanalyzer.goalGickAnalyze(17));
     * System.out.println("backPassAnalyze " + dbanalyzer.backPassAnalyze(17));
     * System.out.println("ballOutAnalyze " + dbanalyzer.ballOutAnalyze(17));
     * System.out.println("connerKickAnalyze " +
     * dbanalyzer.connerKickAnalyze(17)); System.out.println("freeKickAnalyze " +
     * dbanalyzer.freeKickAnalyze(17)); System.out.println("freeKickFaultAnalyze " +
     * dbanalyzer.freeKickFaultAnalyze(17)); System.out.println("kickInAnalyze " +
     * dbanalyzer.kickInAnalyze(17)); System.out.println("offSideAnalyze " +
     * dbanalyzer.offSideAnalyze(17));
     */
  }
}
